﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZhouYu.DreamTour.Core;
using ZhouYu.DreamTour.DAL.IRepository;
using ZhouYu.DreamTour.Model;
using ZhouYu.DreamTour.ViewModel;

namespace ZhouYu.DreamTour.DAL.Repository
{
    public class UserRepository : IUserRepository
    {

        /// <summary>
        /// 用户登录方法
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public UserInfo LoginUserInfo(UserInfo userInfo)
        {
            //sql语句
            string sql = "select User_Id,User_Name,User_Pwd from UserInfo where User_Name=@User_Name and User_Pwd=@User_Pwd";
            //调用查询方法
            UserInfo userInfo1 = DapperHelper<UserInfo>.QuerySingleOrDefault(sql, userInfo);
            return userInfo1;
        }


        /// <summary>
        /// 修改上一次登录时间和当前登录IP
        /// </summary>
        /// <param name="adminInfo"></param>
        /// <returns></returns>
        public int UpdateLoginTime(UserInfo userInfo)
        {
            //获取IP地址
            userInfo.User_LoginIp = LocalIP.GetLocalIP();
            //sql语句
            string sql = "update UserInfo set User_LoginTime=@User_LoginTime,User_LoginIp=@User_LoginIp where User_Name=@User_Name";
            userInfo.User_LoginTime = DateTime.Now;
            //调用修改方法
            int userInfo1 = DapperHelper<UserInfo>.Execute(sql, userInfo);
            return userInfo1;
        }


        /// <summary>
        /// 用户手机注册
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int AddUserInfo(UserInfo userInfo)
        {
            //sql语句
            string sql = "insert into UserInfo(User_Name,User_RealName,User_Pwd,User_Sex,User_Telphone,User_VoucherCount,User_TravelVoCount,User_CreateTime,User_UpdateTime) values(@User_Name,default,@User_Pwd,2,@User_Telphone,@User_VoucherCount,@User_TravelVoCount,@User_CreateTime,@User_UpdateTime)";
            //赋值
            userInfo.User_Name = userInfo.User_Telphone.ToString();
            userInfo.User_VoucherCount = 0;
            userInfo.User_TravelVoCount = 0;
            userInfo.User_CreateTime = DateTime.Now;
            userInfo.User_UpdateTime = DateTime.Now;
            //调用添加方法
            int userInfo1 = DapperHelper<UserInfo>.Execute(sql, userInfo);
            return userInfo1;
        }


        /// <summary>
        /// 用户邮箱注册
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int AddUserEmailInfo(UserInfo userInfo)
        {
            int result = -1;
            //sql语句
            string sql = "insert into UserInfo(User_Name,User_RealName,User_Pwd,User_Sex,User_Email,User_VoucherCount,User_TravelVoCount,User_CreateTime,User_UpdateTime) values(@User_Name,default,@User_Pwd,2,@User_Email,@User_VoucherCount,@User_TravelVoCount,@User_CreateTime,@User_UpdateTime)";
            //赋值
            userInfo.User_Name = userInfo.User_Email.ToString();
            userInfo.User_VoucherCount = 0;
            userInfo.User_TravelVoCount = 0;
            userInfo.User_CreateTime = DateTime.Now;
            userInfo.User_UpdateTime = DateTime.Now;
            //调用添加方法
            int userInfo1 = DapperHelper<UserInfo>.Execute(sql, userInfo);
            return userInfo1;
        }


        /// <summary>
        /// 查询邮箱验证码是否存在
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int CheckEmailCode(EmailInfo emailInfo)
        {
            int result = -1;
            //sql语句
            string sql = "select count(1) from EmailInfo where Code=@Code and Email=@Email and ExpireTime>@ExpireTime";
            emailInfo.ExpireTime = DateTime.Now;
            //调用查询方法
            return Convert.ToInt32(DapperHelper<EmailInfo>.ExecuteScalar(sql,emailInfo));
        }


        /// <summary>
        /// 添加邮箱发送记录
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int AddEmailInfo(EmailInfo emailInfo)
        {
            int result = -1;
            //sql语句
            string sql = "insert into EmailInfo(Code,Email,CreateTime,ExpireTime) values(@Code,@Email,@CreateTime,@ExpireTime)";
            emailInfo.CreateTime = DateTime.Now;
            emailInfo.ExpireTime = DateTime.Now.AddMinutes(5);
            //调用添加方法
            return DapperHelper<EmailInfo>.Execute(sql, emailInfo);
        }


        /// <summary>
        /// 查询邮箱是否存在
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int CheckUser_Email(UserInfo userInfo)
        {
            int result = -1;
            //sql语句
            string sql = "select User_Email from UserInfo where User_Email=@User_Email";
            //调用查询方法
            UserInfo userInfo1 = DapperHelper<UserInfo>.QuerySingleOrDefault(sql, userInfo);
            if (userInfo1 != null)
            {
                result = 1;
            }
            return result;
        }

        /// <summary>
        /// 查询手机号方法
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int CheckUserTel(UserInfo userInfo)
        {
            int result = -1;
            //sql语句
            string sql = "select User_Telphone from UserInfo where User_Telphone=@User_Telphone";
            //调用查询方法
            UserInfo userInfo1 = DapperHelper<UserInfo>.QuerySingleOrDefault(sql, userInfo);
            if (userInfo1 != null)
            {
                result = 1;
            }
            return result;
        }

        /// <summary>
        /// 根据父Id显示网站底部导航展示数据
        /// </summary>
        /// <param name="ColumnId"></param>
        /// <returns></returns>
        public List<Webcolumn> QueryColumn(int ColumnId)
        {   //查询sql语句
            string sql = $"select *from webcolumn where  WebColumn_ParentId={ColumnId}";
            //调用方法
            List<Webcolumn> webcolumns = DapperHelper<Webcolumn>.QueryList(sql, null);
            return webcolumns;
        }

        /// <summary>
        /// 根据版权名显示网站信息
        /// </summary>
        /// <param name="Copyright"></param>
        /// <returns></returns>
        public Website QueryWebsite(string Copyright)
        {
            //查询sql语句
            string sql = $"SELECT *FROM Website  WHERE Website_Copyright='{Copyright}'";
            //调用查询方法
            Website website = DapperHelper<Website>.QuerySingleOrDefault(sql, null);
            return website;
        }

        /// <summary>
        /// 根据id查询用户信息
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public UserInfo GetUserById(int id)
        {
            //创建UserInfo对象参数化
            UserInfo userInfo = new UserInfo();
            userInfo.User_Id = id;
            //sql语句
            string sql = "select * from UserInfo where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<UserInfo>.QuerySingleOrDefault(sql, userInfo);
        }


        /// <summary>
        /// 根据id查询用户现金账户信息
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public Cashnumber GetCashById(int id)
        {
            //创建Cashnumber对象参数化
            Cashnumber cashnumber = new Cashnumber();
            cashnumber.User_Id = id;
            //sql语句
            string sql = "select Cash_balance from CashNumber where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<Cashnumber>.QuerySingleOrDefault(sql, cashnumber);
        }


        /// <summary>
        /// 根据id查询用户订单信息
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public List<OrderInfo> GetOrderById(int id)
        {
            //创建Cashnumber对象参数化
            OrderInfo order = new OrderInfo();
            order.User_Id = id;
            //sql语句
            string sql = "select * from OrderInfo where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<OrderInfo>.QueryList(sql, order);
        }

        /// <summary>
        /// 根据id查询用户抵用卷信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<VoucherInfo> GetVoucherById(int id)
        {
            //创建Cashnumber对象参数化
            Voucher voucher = new Voucher();
            voucher.User_Id = id;
            //sql语句
            string sql = @"select a.Voucher_Id,a.Voucher_balance,a.Voucher_CreateTime,b.VoucherRecord_Channel,b.VoucherRecord_Effective from voucher as a
                        inner join voucher_record as b on a.Voucher_Id = b.Voucher_Id
                        where a.User_Id = @User_Id AND b.VoucherRecord_OperationMethod = '获取' AND a.Voucher_State=1";
            //调用查询方法 
            return DapperHelper<VoucherInfo>.QueryList(sql, voucher);
        }

        /// <summary>
        /// 根据id查询用户抵用卷信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<TouristVolInfo> GetTourVo(int id)
        {
            //创建Cashnumber对象参数化
            Travelvo travelvo = new Travelvo();
            travelvo.User_Id = id;
            //sql语句
            string sql = @"select a.TravelVo_Id,a.TravelVo_balance,a.TravelVo_CardPwd,b.TravelVoRecord_CreateTime,b.TravelVoRecord_Effective from travelvo as a
                            inner join travelvo_record as b on a.TravelVo_Id=b.TravelVo_Id
                            where a.User_Id=@User_Id AND b.TravelVoRecord_OperationMethod='获取' AND a.TravelVo_State=1;";
            //调用查询方法 
            return DapperHelper<TouristVolInfo>.QueryList(sql, travelvo);
        }


        /// <summary>
        /// 根据主键查询抵用余额卷信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Voucher GetVoucherByKey(int id)
        {
            //创建Cashnumber对象参数化
            Voucher voucher = new Voucher();
            voucher.Voucher_Id = id;
            //sql语句
            string sql = @"select Voucher_balance from Voucher where Voucher_Id=@Voucher_Id";
            //调用查询方法 
            return DapperHelper<Voucher>.QuerySingleOrDefault(sql, voucher);
        }



        /// <summary>
        /// 修改用户抵用卷余额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int EditUserVoucher(UserInfo user)
        {
            user.User_UpdateTime = DateTime.Now;
            //sql语句
            string sql = @"Update UserInfo set User_VoucherCount=@User_VoucherCount,User_UpdateTime=@User_UpdateTime where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<Voucher>.Execute(sql, user);
        }

        /// <summary>
        /// 修改抵用卷状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int EditVoucherState(Voucher voucher)
        {
            //sql语句
            string sql = @"Update Voucher set Voucher_State=@Voucher_State where Voucher_Id=@Voucher_Id";
            //调用查询方法 
            return DapperHelper<Voucher>.Execute(sql, voucher);
        }


        /// <summary>
        /// 根据主键查询旅游卷额卷信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Travelvo GetTravelVoByKey(int id)
        {
            //创建Cashnumber对象参数化
            Travelvo travelvo = new Travelvo();
            travelvo.TravelVo_Id = id;
            //sql语句
            string sql = @"select TravelVo_balance from Travelvo where TravelVo_Id=@TravelVo_Id";
            //调用查询方法 
            return DapperHelper<Travelvo>.QuerySingleOrDefault(sql, travelvo);
        }

        /// <summary>
        /// 修改用户旅游卷余额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int EditUserTravelVo(UserInfo user)
        {
            user.User_UpdateTime = DateTime.Now;
            //sql语句
            string sql = @"Update UserInfo set User_TravelVoCount=@User_TravelVoCount,User_UpdateTime=@User_UpdateTime where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<Voucher>.Execute(sql, user);
        }


        /// <summary>
        /// 修改旅游卷状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int EditTravelVoState(Travelvo travelvo)
        {
            //sql语句
            string sql = @"Update Travelvo set TravelVo_State=@TravelVo_State where TravelVo_Id=@TravelVo_Id";
            //调用查询方法 
            return DapperHelper<Voucher>.Execute(sql, travelvo);
        }


        /// <summary>
        /// 校验旅游卷密码是否正确
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int CheckTravelVoPwd(Travelvo travelvo)
        {
            //sql语句
            string sql = @"select Count(1) from Travelvo where TravelVo_CardPwd=@TravelVo_CardPwd AND TravelVo_Id=@TravelVo_Id";
            //调用查询方法 
            return Convert.ToInt32(DapperHelper<Voucher>.ExecuteScalar(sql, travelvo));
        }


        /// <summary>
        /// 获取用户旅游订单集合
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<OrderInfo> GetTourOrder(int id)
        {
            //创建OrderInfo对象参数化
            OrderInfo order = new OrderInfo();
            order.User_Id = id;
            //sql语句
            string sql = "select * from OrderInfo where User_Id=@User_Id AND Order_Type='旅游'";
            //调用查询方法 
            return DapperHelper<OrderInfo>.QueryList(sql, order);
        }

        /// <summary>
        /// 获取用户团购订单集合
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<OrderInfo> GetGroupOrder(int id)
        {
            //创建OrderInfo对象参数化
            OrderInfo order = new OrderInfo();
            order.User_Id = id;
            //sql语句
            string sql = "select * from OrderInfo where User_Id=@User_Id AND Order_Type='团购'";
            //调用查询方法 
            return DapperHelper<OrderInfo>.QueryList(sql, order);
        }


        /// <summary>
        /// 获取用户酒店订单集合
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<OrderInfo> GetHotelOrder(int id)
        {
            //创建OrderInfo对象参数化
            OrderInfo order = new OrderInfo();
            order.User_Id = id;
            //sql语句
            string sql = "select * from OrderInfo where User_Id=@User_Id AND Order_Type='酒店'";
            //调用查询方法 
            return DapperHelper<OrderInfo>.QueryList(sql, order);
        }

        /// <summary>
        /// 获取用户餐饮订单集合
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<OrderInfo> GetCateringOrder(int id)
        {
            //创建OrderInfo对象参数化
            OrderInfo order = new OrderInfo();
            order.User_Id = id;
            //sql语句
            string sql = "select * from OrderInfo where User_Id=@User_Id AND Order_Type='餐饮'";
            //调用查询方法 
            return DapperHelper<OrderInfo>.QueryList(sql, order);
        }

        /// <summary>
        /// 获取用户特产订单集合
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<OrderInfo> GetGoodsOrder(int id)
        {
            //创建OrderInfo对象参数化
            OrderInfo order = new OrderInfo();
            order.User_Id = id;
            //sql语句
            string sql = "select * from OrderInfo where User_Id=@User_Id AND Order_Type='特产'";
            //调用查询方法 
            return DapperHelper<OrderInfo>.QueryList(sql, order);
        }
        #region 根据拥护ID查询现金账号表
        /// <summary>
        /// 根据拥护ID查询现金账号表
        /// </summary>
        /// <param name="User_Id"></param>
        /// <returns></returns>
        public Cashnumber GetCashnumber(int User_Id)
        {
            string sql = @"SELECT * FROM CashNumber WHERE User_Id=@User_Id";
            DynamicParameters dynamicparameters = new DynamicParameters();
            dynamicparameters.Add("@User_Id", User_Id);
            return DapperHelper<Cashnumber>.QuerySingleOrDefault(sql, dynamicparameters);
        }

        #endregion
        /// <summary>
        /// 修改当前用户密码
        /// </summary>
        /// <returns></returns>
        public int Changepwd(string Pwd, int UserId)
        {
            UserInfo user = new UserInfo();
            user.User_Pwd = Pwd;
            user.User_Id = UserId;
            string sql = $"UPDATE userinfo SET User_Pwd =@User_Pwd WHERE User_Id = @User_Id";
            int userInfo = DapperHelper<UserInfo>.Execute(sql, user);
            return userInfo;
        }
        /// <summary>
        /// 获取积分列表
        /// Custompoints:自定义实体类 包括需要展示的字段
        /// </summary>
        /// <returns></returns>
        public List<Custompoints> IntegralList(string Createtime, string Effective, int UserId)
        {
            string sql= $"SELECT *FROM  Integral_Record INNER JOIN integral ON Integral_Record.Integral_Id= integral.Integral_Id INNER JOIN userinfo ON userinfo.User_Id = integral.User_Id  WHERE  Userinfo.User_Id={UserId}";
            if (Createtime != null)
            {
                sql += $"  and IntegralRecord_CreateTime LIKE '%{Createtime}%'";
            }
            if(Effective != null)
            {
                sql += $"  and  IntegralRecord_Effective LIKE '%{Effective}%'";
            }
 
            List<Custompoints> custompoints= DapperHelper<Custompoints>.QueryList(sql, null);
            return custompoints;
        }

        /// <summary>
        ///根据Id查询用户积分
        /// Custompoints:自定义实体类 包括需要展示的字段
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public Custompoints Queryintegral(int UserId)
        {
            string sql = $"SELECT *FROM  Integral INNER JOIN Userinfo ON Integral.User_Id= Userinfo.User_Id  WHERE userinfo.User_Id ={UserId}";
            Custompoints custompoints = DapperHelper<Custompoints>.QuerySingle(sql, null);
            return custompoints;
        }


        /// <summary>
        /// 获取地址列表关联
        /// </summary>
        /// <returns></returns>
        public List<DeliveryAddress> AddressList(int UserId)
        {
            string sql = $"SELECT * FROM DeliveryAddress INNER JOIN UserInfo ON DeliveryAddress.User_Id=UserInfo.User_Id  WHERE UserInfo.User_Id='{UserId}'";
            List<DeliveryAddress> addresses = DapperHelper<DeliveryAddress>.QueryList(sql, null);
            return addresses;
        }
        ///<summary>
        ///根据Id删除配送地址
        ///</summary>
        ///<returns</resturns>
        public int DeleteAddress(int Id)
        {
            DeliveryAddress deliveryAddress = new DeliveryAddress();
            deliveryAddress.Delivery_Id = Id;
            string sql = $"DELETE  FROM Deliveryaddress WHERE Delivery_Id=@Delivery_Id";
            int result = DapperHelper<DeliveryAddress>.Execute(sql,deliveryAddress);
            return result;
        }
        /// <summary>
        /// 增加配送地址
        /// </summary>
        /// <param name="address"></param>
        /// <returns></returns>
        public int AddAddress(DeliveryAddress address)
        {

            string sql = "INSERT INTO DeliveryAddress(Delivery_Name, Delivery_Adress, Delivery_Zipcode, Delivery_TelPhone,BackColumn_CreateTime,User_Id) VALUES(@Delivery_Name,@Delivery_Adress,@Delivery_Zipcode,@Delivery_TelPhone,@BackColumn_CreateTime,@User_Id)";
            //赋当前添加时间
            address.Delivery_CreateTime = DateTime.Now;
            //调用添加方法
            int deliveryAddres = DapperHelper<DeliveryAddress>.Execute(sql, address);
            return deliveryAddres;
        }

        //修改配送地址
        public int UpdateAddress(DeliveryAddress address)
        {
            string sql = @"UPDATE DeliveryAddress SET Delivery_Name = @Delivery_Name,
            Delivery_Adress=@Delivery_Adress,
            Delivery_Zipcode=@Delivery_Zipcode,
            Delivery_TelPhone=@Delivery_TelPhone   WHERE Delivery_Id = @Delivery_Id";
            //赋当前修改时间
            address.Delivery_UpdateTime = DateTime.Now;
            //调用添加方法
            int deliveryAddres = DapperHelper<DeliveryAddress>.Execute(sql, address);
            return deliveryAddres;
        }
        /// <summary>
        /// 根据用户Id查询待点评次数
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public Usercomment QueryUsertobecommentedon(int UserId)
         {
            string sql = $"SELECT *FROM  UserInfo INNER JOIN UserComment ON UserInfo.User_Id= UserComment.User_Id WHERE UserInfo.User_Id = {UserId}";
            Usercomment  usercomment= DapperHelper<Usercomment>.QuerySingle(sql, null);
            return usercomment;
        }

        public OrderInfo Query(string Name)
        {
            string sql = $"SELECT *FROM  UserInfo INNER JOIN OrderInfo ON UserInfo.User_Id= OrderInfo.User_Id WHERE User_Name = {Name}";
            OrderInfo orderInfo = DapperHelper<OrderInfo>.QueryFirstOrDefault(sql, null);
            return orderInfo;
        }



        /// <summary>
        /// 获取发起人信息表最后一条记录的主键
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Originator GetOriginatorLast()
        {
            //sql语句
            string sql = "select Originator_Id from Originator order by Originator_Id DESC limit 1";
            //调用查询方法 
            return DapperHelper<Originator>.QuerySingleOrDefault(sql, null);
        }


        /// <summary>
        /// 获取结伴同行表最后一条记录的主键
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Colleague GetColleagueLast()
        {
            //sql语句
            string sql = "select Colleague_Id from Colleague order by Colleague_Id DESC limit 1";
            //调用查询方法 
            return DapperHelper<Colleague>.QuerySingleOrDefault(sql, null);
        }

        /// <summary>
        /// 添加发起人信息表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AddOriginator(Originator originator)
        {
            //sql语句
            string sql = "insert into Originator(Originator_Name,Originator_Sex,Originator_Email,Originator_TelPhone,Originator_Content,Originator_UpdateTime,Originator_CreateTime) values(@Originator_Name,@Originator_Sex,@Originator_Email,@Originator_TelPhone,@Originator_Content,@Originator_UpdateTime,@Originator_CreateTime)";
            //调用查询方法 
            return DapperHelper<Originator>.Execute(sql, originator);
        }

        /// <summary>
        /// 添加结伴同行表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AddColleague(Colleague colleague)
        {
            //sql语句
            string sql = "insert into Colleague(Route_Id,User_Id,Colleague_Title,Colleague_OutingTime,Colleague_PaymentMethod,Originator_Id,Colleague_UpdateTime,Colleague_CreateTime) values(@Route_Id,@User_Id,@Colleague_Title,@Colleague_OutingTime,@Colleague_PaymentMethod,@Originator_Id,@Colleague_UpdateTime,@Colleague_CreateTime)";
            //调用查询方法 
            return DapperHelper<Colleague>.Execute(sql, colleague);
        }


        /// <summary>
        /// 添加结伴同行人数表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AddNumbers(Number number)
        {
            //sql语句
            string sql = "insert into Number(Colleague_Id,Number_Count,Number_Type,Number_Kind,Number_UpdateTime,Number_CreateTime) values(@Colleague_Id,@Number_Count,@Number_Type,@Number_Kind,@Number_UpdateTime,@Number_CreateTime)";
            //调用查询方法 
            return DapperHelper<Number>.Execute(sql, number);
        }


        /// <summary>
        /// 获取返现现金账号信息记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<CashInfo> GetCashReturn(int id)
        {
            //创建OrderInfo对象参数化
            Cashnumber cashnumber = new Cashnumber();
            cashnumber.User_Id = id;
            //sql语句
            string sql = @"select a.Cash_Id,b.CashRecord_Id,b.CashRecord_Money,b.CashRecord_OperationMethod,b.CashRecord_Channel,b.CashRecord_Effective,b.CashRecord_CreateTime from cashnumber AS a
                            inner join cashnumber_record AS b on a.Cash_Id=b.Cash_Id
                            where a.User_Id=@User_Id AND b.CashRecord_OperationMethod='兑换'";
            //调用查询方法 
            return DapperHelper<CashInfo>.QueryList(sql, cashnumber);
        }



        /// <summary>
        ///根据用户Id查询用户提问记录
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public List<Questions> Queryuserask(int UserId)
        {
            Questions quest = new Questions();
            quest.User_Id = UserId;
            string sql = $"SELECT* FROM  Questions  WHERE User_Id =@User_Id";
            List<Questions>  questions = DapperHelper<Questions>.QueryList(sql,quest);
            return questions;
        }


        /// <summary>
        /// 获取提现现金账号信息记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<CashInfo> GetCash(int id)
        {
            //创建OrderInfo对象参数t
            Cashnumber cashnumber = new Cashnumber();
            cashnumber.User_Id = id;
            //sql语句
            string sql = @"select a.Cash_Id,b.CashRecord_State,b.CashRecord_Id,b.CashRecord_Money,b.CashRecord_OperationMethod,b.CashRecord_Channel,b.CashRecord_Effective,b.CashRecord_CreateTime from cashnumber AS a
                            inner join cashnumber_record AS b on a.Cash_Id=b.Cash_Id
                            where a.User_Id=@User_Id AND b.CashRecord_OperationMethod='提现'";
            //调用查询方法 
            return DapperHelper<CashInfo>.QueryList(sql, cashnumber);
        }
        /// <summary>
        ///根据用户Id查询用户回复记录
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public List<Reply> QueryReply(int UserId)
        {
            string sql = $@"SELECT * FROM (Questions INNER JOIN userinfo ON Questions.User_Id=userinfo.User_Id) INNER JOIN Reply ON 
            Questions.Questions_Id=Reply.Questions_Id WHERE userinfo.User_Id={UserId}";
            List<Reply> replies = DapperHelper<Reply>.QueryList(sql, null);
            return replies;
        }


        /// <summary>
        /// 获取返现现金账号信息记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CashInfo GetCashReturnCount(int id)
        {
            //创建OrderInfo对象参数化
            Cashnumber cashnumber = new Cashnumber();
            cashnumber.User_Id = id;
            //sql语句
            string sql = @"select SUM(b.CashRecord_Money) AS CashRecord_Money from cashnumber AS a
                            inner join cashnumber_record AS b on a.Cash_Id=b.Cash_Id
                            where a.User_Id=@User_Id AND b.CashRecord_OperationMethod='兑换'";
            //调用查询方法 
            return DapperHelper<CashInfo>.QuerySingleOrDefault(sql, cashnumber);
        }
      
        /// <summary>
        /// 获取提现现金账号信息记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CashInfo GetCashCount(int id)
        {
            //创建OrderInfo对象参数化
            Cashnumber cashnumber = new Cashnumber();
            cashnumber.User_Id = id;
            //sql语句
            string sql = @"select SUM(b.CashRecord_Money) AS CashRecord_Money from cashnumber AS a
                            inner join cashnumber_record AS b on a.Cash_Id=b.Cash_Id
                            where a.User_Id=@User_Id AND b.CashRecord_OperationMethod='提现'";
            //调用查询方法 
            return DapperHelper<CashInfo>.QuerySingleOrDefault(sql, cashnumber);
        

        }
       
      
        //查询用户点评
        public List<Usercomment> Querycomment(int userId)
        {
            Usercomment usercomment = new Usercomment();
            usercomment.User_Id = userId;
            string sql = "SELECT *FROM usercomment where User_Id=@User_Id ORDER BY User_Comment_Id DESC";
            List<Usercomment> usercomments = DapperHelper<Usercomment>.QueryList(sql, usercomment);
            return usercomments;
        }
        //删除用户点评
        public int Deletecomments (int id)
        {
            Usercomment usercomment = new Usercomment();
            usercomment.User_Comment_Id = id;
            string sql = "DELETE  FROM usercomment WHERE User_Comment_Id = @User_Comment_Id";
            int result = DapperHelper<Usercomment>.Execute(sql, usercomment);
            return result;
        }
        //修改地址状态
        public int Modifyaddressstatus(int Id)
        {
            DeliveryAddress delivery = new DeliveryAddress();
            delivery.Delivery_Id = Id;
            //修改状态为1
            string sql = " UPDATE deliveryaddress SET Delivery_state=1 WHERE Delivery_Id = @Delivery_Id";
            return DapperHelper<DeliveryAddress>.Execute(sql, delivery);
        }
        //修改地址状态全部为0
        public int UpdateAlladdressStates(int UserId)
        {
            //修改状态全部为0
             string sql = $" UPDATE deliveryaddress SET Delivery_state=0 WHERE User_Id={UserId}";
             int result=   DapperHelper<DeliveryAddress>.Execute(sql, null);
             return result;
        }
    

        /// <summary>
        /// 获取用户现金账户余额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Cashnumber GetCashbalance(int id)
        {
            //创建对象参数化
            Cashnumber cashnumber = new Cashnumber();
            cashnumber.User_Id = id;
            //sql语句
            string sql = @"select * from cashnumber where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<Cashnumber>.QuerySingleOrDefault(sql, cashnumber);
        }


        /// <summary>
        /// 添加提现记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AddCashOut(CashOut cash)
        {
            //sql语句
            string sql = @"insert into CashOut(User_Id,CashOut_Name,CashOut_Money,CashOut_BankNum,CashOut_From,CashOut_Type,CashOut_CreateTime,CashRecord_Id) values(@User_Id,@CashOut_Name,@CashOut_Money,@CashOut_BankNum,@CashOut_From,@CashOut_Type,@CashOut_CreateTime,@CashRecord_Id)";
            //调用查询方法 
            return DapperHelper<Cashnumber>.Execute(sql, cash);
        }


        /// <summary>
        /// 添加现金账号操作记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AddCashnumber_record(Cashnumber_record cashnumber_Record)
        {
            //sql语句
            string sql = @"insert into Cashnumber_Record(Cash_Id,CashRecord_State,CashRecord_Money,CashRecord_OperationMethod,CashRecord_Channel,CashRecord_Effective,CashRecord_CreateTime) values(@Cash_Id,@CashRecord_State,@CashRecord_Money,@CashRecord_OperationMethod,@CashRecord_Channel,@CashRecord_Effective,@CashRecord_CreateTime)";
            //调用查询方法 
            return DapperHelper<Cashnumber>.Execute(sql, cashnumber_Record);
        }


        /// <summary>
        /// 获取现金账号操作表最后一条记录的主键
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Cashnumber_record GetCashRecordLast()
        {
            //sql语句
            string sql = "select * from Cashnumber_Record order by CashRecord_Id DESC limit 1";
            //调用查询方法 
            return DapperHelper<Cashnumber_record>.QuerySingleOrDefault(sql, null);
        }


        /// <summary>
        /// 修改现金账号余额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int EditCashBalance(Cashnumber cashnumber)
        {
            //sql语句
            string sql = @"Update CashNumber set Cash_balance=Cash_balance-@Cash_balance where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<Cashnumber>.Execute(sql, cashnumber);
        }

        /// <summary>
        /// 充值现金账号添加余额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int RechargeCashBalance(Cashnumber cashnumber)
        {
            //sql语句
            string sql = @"Update CashNumber set Cash_balance=Cash_balance+@Cash_balance where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<Cashnumber>.Execute(sql, cashnumber);
        }


        /// <summary>
        /// 增加账户冻结金额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AddCashFreeze(Cashnumber cashnumber)
        {
            //sql语句
            string sql = @"Update CashNumber set Cash_freeze=Cash_freeze+@Cash_balance where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<Cashnumber>.Execute(sql, cashnumber);
        }


        /// <summary>
        /// 减少账户冻结金额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int ReduceCashFreeze(CashInfo cashInfo)
        {
            Cashnumber cashnumber = new Cashnumber();
            cashnumber.Cash_Id = cashInfo.Cash_Id;
            cashnumber.Cash_balance = cashInfo.CashRecord_Money;
            //sql语句
            string sql = @"Update CashNumber set Cash_freeze=Cash_freeze-@Cash_balance where Cash_Id=@Cash_Id";
            //调用查询方法 
            return DapperHelper<Cashnumber>.Execute(sql, cashnumber);
        }


        /// <summary>
        /// 查询审核未通过的信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<GetCashUser> QueryExamine()
        {
            //sql语句
            string sql = @"select a.CashOut_BankNum,a.CashOut_From,a.CashOut_Money,b.User_Telphone,b.User_NickName,CashOut_CreateTime,c.CashRecord_State from cashout as a
							inner join userinfo as b on b.User_Id=a.User_Id
                            inner join cashnumber_record as c on c.CashRecord_Id=a.CashRecord_Id
							where c.CashRecord_State=0";
            //调用查询方法 
            return DapperHelper<GetCashUser>.QueryList(sql, null);
        }


        /// <summary>
        /// 提现审核通过
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AuditPass()
        {
            //sql语句
            string sql = @"Update cashnumber_record set CashRecord_State=1 where CashRecord_State=0";
            //调用查询方法 
            return DapperHelper<Cashnumber_record>.Execute(sql, null);
        }


        /// <summary>
        /// 添加旅游卷
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AddTravelVo(Travelvo travelvo)
        {
            //sql语句
            string sql = @"insert into travelvo(TravelVo_balance,TravelVo_CreateTime,User_Id,TravelVo_State,TravelVo_CardPwd) values(@TravelVo_balance,@TravelVo_CreateTime,@User_Id,@TravelVo_State,@TravelVo_CardPwd)";
            //调用查询方法 
            return DapperHelper<Cashnumber>.Execute(sql, travelvo);
        }

        /// <summary>
        /// 添加旅游卷操作记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AddTravelVoRecord(Travelvo_record travelvo_Record)
        {
            //sql语句
            string sql = @"insert into travelvo_record(TravelVo_Id,TravelVoRecord_Money,TravelVoRecord_OperationMethod,TravelVoRecord_Effective,TravelVoRecord_CreateTime) values(@TravelVo_Id,@TravelVoRecord_Money,@TravelVoRecord_OperationMethod,@TravelVoRecord_Effective,@TravelVoRecord_CreateTime)";
            //调用查询方法 
            return DapperHelper<Travelvo_record>.Execute(sql, travelvo_Record);
        }

        /// <summary>
        /// 获取旅游卷最后一条记录的主键
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Travelvo GetTravelVoLast(Travelvo travelvo)
        {
            //sql语句
            string sql = "select TravelVo_Id from travelvo where User_Id=@User_Id order by TravelVo_Id DESC limit 1;";
            //调用查询方法 
            return DapperHelper<Travelvo>.QuerySingleOrDefault(sql, travelvo);
        }


        /// <summary>
        /// 查询当前用户申请的结伴同行集合
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Colleague> GetColleagueByUId(Colleague colleague)
        {
            //sql语句
            string sql = @"select * from Colleague where User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<Colleague>.QueryList(sql, colleague);
        }

        /// <summary>
        /// 查询所有结伴同行集合
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Colleague> GetColleague()
        {
            //sql语句
            string sql = @"select * from Colleague";
            //调用查询方法 
            return DapperHelper<Colleague>.QueryList(sql, null);
        }


        /// <summary>
        /// 修改用户基本信息
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int EditUserInfo(UserInfo userInfo)
        {
            //给修改时间赋值
            userInfo.User_UpdateTime = DateTime.Now;
            //sql语句
            string sql = "Update UserInfo set User_NickName=@User_NickName,User_RealName=@User_RealName,User_Sex=@User_Sex,User_Adress=@User_Adress,User_Birthday=@User_Birthday,User_UpdateTime=@User_UpdateTime where User_Id=@User_Id";
            //调用添加方法
            int userInfo1 = DapperHelper<UserInfo>.Execute(sql, userInfo);
            return userInfo1;
        }


        /// <summary>
        /// 修改用户手机号
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int EditUserTel(UserInfo userInfo)
        {
            //给修改时间赋值
            userInfo.User_UpdateTime = DateTime.Now;
            //sql语句
            string sql = "Update UserInfo set User_Telphone=@User_Telphone where User_Id=@User_Id";
            //调用添加方法
            int userInfo1 = DapperHelper<UserInfo>.Execute(sql, userInfo);
            return userInfo1;
        }


        /// <summary>
        /// 修改用户邮箱
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int EditUserEmail(UserInfo userInfo)
        {
            //给修改时间赋值
            userInfo.User_UpdateTime = DateTime.Now;
            //sql语句
            string sql = "Update UserInfo set User_Email=@User_Email where User_Id=@User_Id";
            //调用添加方法
            int userInfo1 = DapperHelper<UserInfo>.Execute(sql, userInfo);
            return userInfo1;
        }


        /// <summary>
        /// 查询审核未通过的账户id和操作金额
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<CashInfo> QueryExamineCashId()
        {
            //sql语句
            string sql = @"select a.Cash_Id,b.CashRecord_Money from cashnumber as a
                            inner join cashnumber_record as b on b.Cash_Id=a.Cash_Id
                            where b.CashRecord_State=0;";
            //调用查询方法 
            return DapperHelper<CashInfo>.QueryList(sql, null);
        }

        #region 根据ID查询用户信息
        /// <summary>
        /// 根据ID查询用户信息
        /// </summary>
        /// <param name="User_Id"></param>
        /// <returns></returns>
        public UserInfo SelUserInfoById(int User_Id)
        {
            //sql语句
            string sql = @"SELECT * FROM userinfo WHERE User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<UserInfo>.QuerySingleOrDefault(sql,new { User_Id = User_Id });
        }
        #endregion

        #region 绑定银行卡
        /// <summary>
        /// 绑定银行卡
        /// </summary>
        /// <param name="User_Id"></param>
        /// <returns></returns>
        public int BindBank(User_Bank user_Bank)
        {
            //sql语句
            string sql = @"INSERT INTO User_Bank(User_Id,Bank_Num,Bank_Name,Bank_Type,Bank_Form,Bank_Tel,Bank_Logo,Bank_CreateTime,Bank_UpdateTime) VALUES(@User_Id,@Bank_Num,@Bank_Name,@Bank_Type,@Bank_Form,@Bank_Tel,@Bank_Logo,@Bank_CreateTime,@Bank_UpdateTime)";
            //调用查询方法 
            return DapperHelper<User_Bank>.Execute(sql, user_Bank);
        }
        #endregion


        #region 获取当前登录用户绑定的银行卡集合
        /// <summary>
        /// 获取当前登录用户绑定的银行卡集合
        /// </summary>
        /// <param name="User_Id"></param>
        /// <returns></returns>
        public List<User_Bank> QueryUserBank(User_Bank user_Bank)
        {
            //sql语句
            string sql = @"SELECT * FROM User_Bank WHERE User_Id=@User_Id";
            //调用查询方法 
            return DapperHelper<User_Bank>.QueryList(sql, user_Bank);
        }
        #endregion

        #region 解绑指定银行卡
        /// <summary>
        /// 解绑指定银行卡
        /// </summary>
        /// <param name="User_Id"></param>
        /// <returns></returns>
        public int DelBank(User_Bank user_Bank)
        {
            //sql语句
            string sql = @"DELETE FROM User_Bank WHERE Bank_Id=@Bank_Id";
            //调用查询方法 
            return DapperHelper<User_Bank>.Execute(sql, user_Bank);
        }
        #endregion

    }
}